package com.lingxu.module.collection.service.impl;

import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.hutool.core.date.DateUtil;
import com.alibaba.fastjson.JSON;
import com.deepoove.poi.XWPFTemplate;
import com.lingxu.base.common.util.*;
import com.lingxu.module.collection.entity.ModeResultList;
import com.lingxu.module.collection.entity.ModelColumnInfo;
import com.lingxu.module.collection.entity.ReportDetail;
import com.lingxu.module.collection.entity.ReportVo;
import com.lingxu.module.collection.mapper.ReportMapper;
import com.lingxu.module.collection.service.IReportRunService;
import com.lingxu.module.redList.entity.RedList;
import com.lingxu.module.redList.mapper.RedListMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Clob;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @Title: ReportRunService
 * @Package: com.lingxu.module.collection.service.impl
 * @Author: ysj闫世杰
 * @Email: 1105324813@qq.com
 * @Date: Created in 2023/11/10 9:37
 * @Description:
 * @Version: 1.0.0
 */
@Slf4j
@Service
public class ReportRunService implements IReportRunService {

    private static final Logger logger = LoggerFactory.getLogger(ReportService.class);

    @Autowired
    private ReportMapper reportMapper;
    @Autowired
    private RedListMapper redListMapper;

    @Value(value = "${uploadUrl}")
    private String uploadpath;
    @Value(value = "${isEncrypt}")
    private String isEncrypt;

    @Override
    public void exportReportByRun(ReportVo vo, HttpServletResponse response) {
        try {
            String filePath = uploadpath + "/report/";
            Map<String,Object> map = getMapinfoNew(vo,filePath);
            String modelname = vo.getModelName();
            String fileName = modelname + "_" +System.currentTimeMillis() + ".doc";
            createWordFile(map,filePath + fileName);
            List<ModeResultList> resultLists = (List<ModeResultList>) map.get("modeResultList");
            if(resultLists != null && resultLists.size() > 0){
                //多个文件打成zip下载
                String word = filePath + fileName;
                ArrayList<File> fileList = new ArrayList<>();
                File wordFile = new File(word);
                fileList.add(wordFile);
                for (ModeResultList mr : resultLists){
                    if(!StringUtils.isEmpty(mr.getExcelFiles())){
                        File file = new File(filePath + mr.getExcelFiles());
                        fileList.add(file);
                    }
                }
                String zipName = filePath + modelname + "_" +System.currentTimeMillis() + ".zip";
                //区分是否需要加密压缩包
                if("1".equals(isEncrypt)){
                    ZipUtils.zipFilePwd(zipName,fileList,vo.getTimestamp());
                }else {
                    OutputStream outputStream = new FileOutputStream(zipName);
                    ZipUtils.toZip(fileList,outputStream);
                }
                FileUtils.downloaddocx(zipName,response, false);
                //删除文件
                File zip = new File(zipName);
                zip.delete();
                wordFile.delete();
                for (ModeResultList mr : resultLists){
                    if(!StringUtils.isEmpty(mr.getExcelFiles())){
                        new File(filePath + mr.getExcelFiles()).delete();
                    }
                }
            } else {
                FileUtils.downloaddocx(filePath + fileName,response, true);
                File file = new File(filePath + fileName);
                file.delete();
            }
        }catch (Exception e){
            logger.error("文件下载异常：",e);

        }
    }


    private Map<String, Object> getMapinfoNew(ReportVo report,String filePath) throws SQLException, IOException {
        Map<String,Object> map = new HashMap<>();
        //根据模型id查询基本信息
        ReportDetail detail = reportMapper.findModelDetail(report.getModelid());
        String time= DateUtil.formatDate(new Date());
        String date = time.substring(0,4) + "年" + time.substring(5,7) + "月" + time.substring(8,10) + "日";
        map.put("modelname",detail.getMastermodelname());
        map.put("date",date);
        map.put("username",detail.getRegionName() + "-" + detail.getName());
        map.put("areaname",detail.getRegionName());
        //根据modelid查询主模型id的数据来源
        Map mm = reportMapper.getDataFromsByModelId(report.getModelid());
        if(mm != null){
            String dataFroms = ToolUtil.clobToString((Clob) mm.get("DATAFROMS"));
            //数据来源
            if(!StringUtils.isBlank(dataFroms)){
                map.put("froms",dataFroms);
            }else {
                map.put("froms","暂未填写");
            }
            String qlfxd = (String) mm.get("QLFXD");
            map.put("ypmd",qlfxd);
        }
        map.put("mxms",detail.getMastermodelremark());
//        //判断是否首次生成编码
//        if(StringUtils.isEmpty(detail.getReportno())){
//            //首次生成编码
//            String reportno = reportNoHandler(detail);
//            //更新表
//            reportMapper.updateReportNo(report.getModelid(),reportno);
//            map.put("reportno",reportno);
//        }else {
//            map.put("reportno",detail.getReportno());
//        }
        //比对规则
        StringBuilder content = new StringBuilder();
        List<String> jdgz = reportMapper.findModelJdgz(detail.getMastermodelid());
        for(String s : jdgz){
            content.append(s).append(";");
        }
        if(!StringUtils.isEmpty(content)) {
            content.replace(content.lastIndexOf(";"),content.length(),"。");
            map.put("content", content);
        }
        //涉及表数量
        List<String> ids = Arrays.asList(report.getModelid().split(","));
        int tableNum = reportMapper.getTableNum(ids);
        map.put("tablenum",tableNum);
        //查询比对总量
        int dataNum = reportMapper.findDataNumByModelId(report.getModelid());
        map.put("datanum",dataNum);
        map.put("resultnum",report.getResultTotal());
        int sum = reportMapper.getSum(report.getModelid());
        //红名单数量为0不显示
        if(report.getRedTotal() < 1){
            map.put("rednum", "");
        }else {
            map.put("rednum", "其中，红名单涉及" + report.getRedTotal() + "人。");
        }
        List<ModeResultList> resultList = new ArrayList<>();
        String resultColumns = reportMapper.getResultColumns(report.getResultTable(),report.getModelid());
        List<ModelColumnInfo> modelColumnInfoList = JSON.parseArray(resultColumns, ModelColumnInfo.class);
        Map<String, String> head = new LinkedHashMap<>();
        if (modelColumnInfoList.size() > 0) {
            for (ModelColumnInfo modelColumnInfo : modelColumnInfoList) {
                head.put(modelColumnInfo.getEnname().toUpperCase(), modelColumnInfo.getCnname());
            }
            //查询总数
            String sqlCou = "select count(*) from DIMENRESULT." + report.getResultTable();
            Integer cou = reportMapper.getResultCount(sqlCou);
            //查询列表前20
            String columns = (StringUtils.join(head.keySet().toArray(), ","));
            String sql = "select " + columns + ", '否' as ISRED" + " from DIMENRESULT." + report.getResultTable();

            //新增默认字段是否红名单
            head.put("ISRED","是否红名单");

            List<LinkedHashMap<String, Object>> dataList = reportMapper.getResultList(sql);

            //用户存放红名单人员信息
            List<LinkedHashMap<String, Object>> contain = new ArrayList<>();

            //查询红名单
            List<RedList> redLists = redListMapper.openFindRedListApi(null);
            Iterator<LinkedHashMap<String, Object>> iterator = dataList.iterator();
            while (iterator.hasNext()) {
                LinkedHashMap<String, Object> m = iterator.next();
                if(m != null && m.size() > 0){
                    for (String keys : m.keySet()) {
                        if (keys.contains("SFZ") || keys.contains("ZJHM")) {
                            String values = (String) m.get(keys);
                            for (RedList red : redLists) {
                                String idcard = red.getIdcard();
                                if (idcard.equals(values)) {
                                    //放入红名单人员list
                                    contain.add(m);
                                    iterator.remove();
                                    System.out.println("过滤红名单数据：" + idcard);
                                    break;
                                }
                            }
                            break;
                        }
                    }
                }
            }

            //判断红名单人员是否大于0
            if(report.getRedTotal() > 0){
                //查询是否已审批
                String state = reportMapper.getApprovestate(report.getId());
                if("1".equals(state)){
                    for(LinkedHashMap<String, Object> m : contain){
                        m.put("ISRED","是");
                    }
                    dataList.addAll(contain);
                }
            }

            StringBuilder sb = new StringBuilder("1、");
            int index = 1;
            if (dataList.size() > 0) {
                List<LinkedHashMap<String, Object>> mapList;
                if (dataList.size() > 20) {
                    mapList = dataList.subList(0, 10);
                } else {
                    mapList = dataList;
                }
                for (Map maps : mapList) {
                    if(maps != null && maps.size() > 0) {
                        for (String key : head.keySet()) {
                            Object o = maps.get(key.toUpperCase());
                            //判断为姓名和身份证号码的字符串进行拼接
                            if (key.contains("xm")) {
                                sb.append(head.get(key)).append(":").append(o).append(",");
                                break;
                            }
                        }
                        for (String key : head.keySet()) {
                            Object o = maps.get(key.toUpperCase());
                            //判断为姓名和身份证号码的字符串进行拼接
                            if (key.contains("sfz") || key.contains("zjhm")) {
                                sb.append(head.get(key)).append(":").append(o).append(",");
                                break;
                            }
                        }
                        for (String key : head.keySet()) {
                            Object o = maps.get(key.toUpperCase());
                            //判断为姓名和身份证号码的字符串进行拼接
                            if (key.contains("dwmc")) {
                                sb.append(head.get(key)).append(":").append(o).append(",");
                                break;
                            }
                        }
                        for (String key : head.keySet()) {
                            Object o = maps.get(key.toUpperCase());
                            //判断为姓名和身份证号码的字符串进行拼接
                            if (key.contains("zw")) {
                                sb.append(head.get(key)).append(":").append(o).append(",");
                            }
                        }
                    }
                    if (sb.length() > 2) {
                        sb.replace(sb.lastIndexOf(","),sb.length(),";");
                        index++;
                        if (index <= mapList.size()) {
                            sb.append("\n").append("    ").append(index).append("、");
                        }
                    }
                }
                ModeResultList modeResultList = new ModeResultList();
                if (sb.length() > 2) {
                    //String infos = sb.toString();
                    modeResultList.setInfos(sb.replace(sb.lastIndexOf(";"),sb.length(),"。").toString());
                } else {
                    modeResultList.setInfos("无法匹配姓名身份证字段");
                }
                //excel附件
                if (cou > 0 || dataList.isEmpty()) {
                    modeResultList.setInto(cou);
                    modeResultList.setExist("部分问题数据如下：");
//                    String excelFileName = System.currentTimeMillis() + ".xls";
                    String excelFileName = detail.getModel() + ".xls";
                    // 如果没有数据，生成一个包含列名的空Excel
                    if (dataList.isEmpty()) {
                        // 创建一个只有列名的空表格
                        List<LinkedHashMap<String, Object>> emptyDataList = new ArrayList<>();
                        // 只需要列名，无数据
                        saveExcel(emptyDataList, head, filePath + excelFileName);
                    } else {
                        // 保存带有数据的Excel
                        dataList.removeAll(Collections.singleton(null));  // 移除全NULL的数据
                        saveExcel(dataList, head, filePath + excelFileName);
                    }

                    modeResultList.setExcelFiles(excelFileName);
                    modeResultList.setExcel("详细完整数据请看附件：" + excelFileName);
                }
                //查询模型信息
                modeResultList.setModel(report.getModelName());
                modeResultList.setModelnum(sum);
                resultList.add(modeResultList);
            }
        }
        map.put("modeResultList",resultList);
        return map;
    }

    //保存Excel
    private void saveExcel(List<LinkedHashMap<String,Object>> dataAll,Map<String,String> headMap,String fileName){
        //要导出的动态列
        List<ExcelExportEntity> beanList = new ArrayList<>();
        for(Map.Entry<String,String> map : headMap.entrySet()){
            beanList.add(new ExcelExportEntity(map.getValue(),map.getKey().toUpperCase()));
        }
        EasyExcelUtils.writeExcelToStream(fileName,beanList,dataAll);
    }


    //生成报告编码
    private String reportNoHandler(ReportDetail detail) {
        //年月
        String yeardate = new SimpleDateFormat("yyyyMM").format(new Date());
        //查询当前地区本月生成模型数量
        String no = "";
        String number = reportMapper.findCountByRegionAndMonth(detail.getRegionName(),yeardate);
        if(StringUtils.isEmpty(number)){
            no = "01";
        }else {
            String s = number.substring(6);
            int count = Integer.parseInt(s);
            if(count < 10){
                no = "0" + count;
            } else {
                no = count + "";
            }
        }
        return yeardate + no;
    }

    private void createWordFile(Map<String,Object> map, String filePath){
        FileOutputStream fos = null;
        XWPFTemplate template = null;
        try {
            Resource resource = new ClassPathResource("template/报告模板.docx");
            ZipSecureFile.setMinInflateRatio(0.001);
            template = XWPFTemplate.compile(resource.getInputStream()).render(map);
            fos = new FileOutputStream(FileUtil.checkExist(filePath));
            template.write(fos);
        }catch (IOException e){
            logger.error("文件创建失败",e);
        }finally {
            if(template != null){
                try {
                    template.close();
                } catch (IOException e) {
                    logger.error("关闭异常");
                }
            }
            if(fos != null){
                try {
                    fos.close();
                } catch (IOException e) {
                    logger.error("关闭异常");
                }
            }
        }
    }
}
